﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spHasFullBackup]
#-- Purpose:		Checks for a valid full backup of a database
#--	Last Update:	11/22/2011
#--					For a complete history - please review comments in Version
#--					Control.
#-- Called By:		[dbo].[spBackupDifferential]
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spHasFullBackup]
(
	@database_name sysname
)
AS
BEGIN
	--- Declare Local Variables
	DECLARE	@HasFullBkp bit
	
	--- Determine the Answer
	SELECT	@HasFullBkp = CAST(CASE	WHEN	d.last_backup_date IS NULL
											OR (
												d.recovery_model_desc <> 'SIMPLE'
												AND (
													d.last_backup_date < d.last_restore_date
													OR d.last_backup_date < d.create_date
												)
											)
									  THEN 0 
									ELSE 1 END AS bit)
	FROM	(
			--- (status & 4) = 4 is Select Into/Bulkcopy
			--- (status & 8) = 8 is Trunc. Log on Chkpt
			SELECT		d1.dbid database_id,
						d1.name,
						(CASE	WHEN (d1.status & 4) = 0 AND (d1.status & 8) = 0 THEN 'FULL'
								WHEN (d1.status & 4) = 0 AND (d1.status & 8) = 8 THEN 'SIMPLE'
								WHEN (d1.status & 4) = 4 AND (d1.status & 8) = 0 THEN 'BULK_LOGGED'
						END) recovery_model_desc,
						d1.crdate create_date,
						(
						SELECT	MAX(restore_date) AS restore_date
						FROM	msdb.dbo.restorehistory
						WHERE	destination_database_name = d1.name
						) last_restore_date,
						(
						SELECT	MAX(backup_start_date) AS backup_date
						FROM	msdb.dbo.backupset
						WHERE	database_name = d1.name 
								AND type = 'D' --full backup
						) last_backup_date
			FROM		master.dbo.sysdatabases d1
			WHERE		d1.name = @database_name
			) d

	--- Return the Answer to the Question
	RETURN	@HasFullBkp
END
